<?php
include_once (dirname(__FILE__) . '/../libs/db.php');

if (!isset($dblang)) { $dblang='en'; }

function pligg_createtables($conn) {

global $dblang;

$sql = 'DROP TABLE IF EXISTS `' . table_categories . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_categories . "` (
  `category__auto_id` int(11) NOT NULL auto_increment,
  `category_lang` varchar(" . strlen($dblang) . ") NOT NULL default " . "'" . $dblang . "',
  `category_id` int(11) NOT NULL default '0',
  `category_parent` int(11) NOT NULL default '0',
  `category_name` varchar(64) NOT NULL default '',
  `category_safe_name` varchar(64) NOT NULL default '',
  `rgt` int(11) NOT NULL default '0',
  `lft` int(11) NOT NULL default '0',
  `category_enabled` int(11) NOT NULL default '1',
  `category_order` int(11) NOT NULL default '0',
  `category_desc` varchar(255) NOT NULL,
  `category_keywords` varchar(255) NOT NULL,
  `category_author_level` enum('normal','admin','god') NOT NULL default 'normal',
  `category_author_group` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`category__auto_id`),
  UNIQUE KEY `key` (`category_name`),
  KEY `category_parent` (`category_parent`),
  KEY `category_safe_name` (`category_safe_name`)
) TYPE = MyISAM;";
echo 'Creating table: \'categories\'...<br />';
mysql_query( $sql, $conn );


$sql = 'DROP TABLE IF EXISTS `' . table_comments . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_comments . "` (
  `comment_id` int(20) NOT NULL auto_increment,
  `comment_randkey` int(11) NOT NULL default '0',
  `comment_parent` int(20) default '0',
  `comment_link_id` int(20) NOT NULL default '0',
  `comment_user_id` int(20) NOT NULL default '0',
  `comment_date` timestamp NOT NULL,
  `comment_karma` smallint(6) NOT NULL default '0',
  `comment_content` text NOT NULL,
  `comment_votes` int(20) NOT NULL default '0',
  PRIMARY KEY  (`comment_id`),
  UNIQUE KEY `comments_randkey` (`comment_randkey`,`comment_link_id`,`comment_user_id`,`comment_parent`),
  KEY `comment_link_id` (`comment_link_id`, `comment_parent`, `comment_date`),
  KEY `comment_link_id_2` (`comment_link_id`,`comment_date`),
  KEY `comment_date` (`comment_date`),
  KEY `comment_parent` (`comment_parent`,`comment_date`)
) TYPE = MyISAM;";
echo 'Creating table: \'comments\'...<br />';
mysql_query( $sql, $conn );


$sql = 'DROP TABLE IF EXISTS `' . table_friends . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_friends . "` (
  `friend_id` int(11) NOT NULL auto_increment,
  `friend_from` bigint(20) NOT NULL default '0',
  `friend_to` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`friend_id`),
  UNIQUE KEY `friends_from_to` (`friend_from`,`friend_to`)
) TYPE = MyISAM;";
echo 'Creating table: \'friends\'...<br />';
mysql_query( $sql, $conn );


$sql = 'DROP TABLE IF EXISTS `' . table_links . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_links . "` (
  `link_id` int(20) NOT NULL auto_increment,
  `link_author` int(20) NOT NULL default '0',
  `link_status` enum('discard','queued','published','abuse','duplicated','page') NOT NULL default 'discard',
  `link_randkey` int(20) NOT NULL default '0',
  `link_votes` int(20) NOT NULL default '0',
  `link_reports` int(20) NOT NULL default '0',
  `link_comments` int(20) NOT NULL default '0',
  `link_karma` decimal(10,2) NOT NULL default '0.00',
  `link_modified` timestamp NOT NULL,
  `link_date` timestamp NOT NULL,
  `link_published_date` timestamp NOT NULL,
  `link_category` int(11) NOT NULL default '0',
  `link_lang` int(11) NOT NULL default '1',
  `link_url` varchar(200) NOT NULL default '',
  `link_url_title` text,
  `link_title` text NOT NULL,
  `link_title_url` varchar(255) default NULL,
  `link_content` text NOT NULL,
  `link_summary` text,
  `link_tags` text,
  `link_field1` varchar(255) NOT NULL default '',
  `link_field2` varchar(255) NOT NULL default '',
  `link_field3` varchar(255) NOT NULL default '',
  `link_field4` varchar(255) NOT NULL default '',
  `link_field5` varchar(255) NOT NULL default '',
  `link_field6` varchar(255) NOT NULL default '',
  `link_field7` varchar(255) NOT NULL default '',
  `link_field8` varchar(255) NOT NULL default '',
  `link_field9` varchar(255) NOT NULL default '',
  `link_field10` varchar(255) NOT NULL default '',
  `link_field11` varchar(255) NOT NULL default '',
  `link_field12` varchar(255) NOT NULL default '',
  `link_field13` varchar(255) NOT NULL default '',
  `link_field14` varchar(255) NOT NULL default '',
  `link_field15` varchar(255) NOT NULL default '',
  `link_group_id` int(20) NOT NULL default '0',
  `link_out` int(11) NOT NULL default '0',
  PRIMARY KEY  (`link_id`),
  KEY `link_author` (`link_author`),
  KEY `link_url` (`link_url`),
  KEY `link_status` (`link_status`),
  KEY `link_title_url` (`link_title_url`),
  KEY `link_date` (`link_date`),
  KEY `link_published_date` (`link_published_date`),
  FULLTEXT KEY `link_url_2` (`link_url`,`link_url_title`,`link_title`,`link_content`,`link_tags`),
  FULLTEXT KEY `link_tags` (`link_tags`),
  FULLTEXT KEY `link_search` (`link_title`,`link_content`,`link_tags`)
) TYPE = MyISAM;";
echo 'Creating table: \'links\'...<br />';
mysql_query( $sql, $conn );


$sql = 'DROP TABLE IF EXISTS `' . table_trackbacks . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_trackbacks . "` (
  `trackback_id` int(10) unsigned NOT NULL auto_increment,
  `trackback_link_id` int(11) NOT NULL default '0',
  `trackback_user_id` int(11) NOT NULL default '0',
  `trackback_type` enum('in','out') NOT NULL default 'in',
  `trackback_status` enum('ok','pendent','error') NOT NULL default 'pendent',
  `trackback_modified` timestamp NOT NULL,
  `trackback_date` timestamp NULL default NULL,
  `trackback_url` varchar(200) NOT NULL default '',
  `trackback_title` text,
  `trackback_content` text,
  PRIMARY KEY  (`trackback_id`),
  UNIQUE KEY `trackback_link_id_2` (`trackback_link_id`,`trackback_type`,`trackback_url`),
  KEY `trackback_link_id` (`trackback_link_id`),
  KEY `trackback_url` (`trackback_url`),
  KEY `trackback_date` (`trackback_date`)
) TYPE = MyISAM;";
echo 'Creating table: \'trackbacks\'...<br />';
mysql_query( $sql, $conn );


$sql = 'DROP TABLE IF EXISTS `' . table_users . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_users . "` (
  `user_id` int(20) NOT NULL auto_increment,
  `user_login` varchar(32) NOT NULL default '',
  `user_level` enum('normal','admin','god') NOT NULL default 'normal',
  `user_modification` timestamp NOT NULL,
  `user_date` timestamp NOT NULL,
  `user_pass` varchar(64) NOT NULL default '',
  `user_email` varchar(128) NOT NULL default '',
  `user_names` varchar(128) NOT NULL default '',
  `user_karma` decimal(10,2) default '10.00',
  `user_url` varchar(128) NOT NULL default '',
  `user_lastlogin` timestamp NOT NULL,
  `user_aim` varchar(64) NOT NULL default '',
  `user_msn` varchar(64) NOT NULL default '',
  `user_yahoo` varchar(64) NOT NULL default '',
  `user_gtalk` varchar(64) NOT NULL default '',
  `user_skype` varchar(64) NOT NULL default '',
  `user_irc` varchar(64) NOT NULL default '',
  `public_email` varchar(64) NOT NULL default '',
  `user_avatar_source` varchar(255) NOT NULL default '',
  `user_ip` varchar(20) default '0',
  `user_lastip` varchar(20) default '0',
  `last_reset_request` timestamp NOT NULL,
  `last_email_friend` timestamp NOT NULL,
  `last_reset_code` varchar(255) default NULL,
  `user_location` varchar(255) default NULL,
  `user_occupation` varchar(255) default NULL,
  `user_categories` VARCHAR(255) NOT NULL default '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19.20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60',
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `user_login` (`user_login`),
  KEY `user_email` (`user_email`)
) TYPE = MyISAM;";
echo 'Creating table: \'users\'...<br />';
mysql_query( $sql, $conn );

$sql = 'DROP TABLE IF EXISTS `' . table_tags . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_tags . "` (
  `tag_link_id` int(11) NOT NULL default '0',
  `tag_lang` varchar(4) NOT NULL default 'en',
  `tag_date` timestamp NOT NULL,
  `tag_words` varchar(64) NOT NULL default '',
  UNIQUE KEY `tag_link_id` (`tag_link_id`,`tag_lang`,`tag_words`),
  KEY `tag_lang` (`tag_lang`,`tag_date`)
) TYPE = MyISAM;";
echo 'Creating table: \'tags\'...<br />';
mysql_query( $sql, $conn );


$sql = 'DROP TABLE IF EXISTS `' . table_votes . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_votes . "` (
  `vote_id` int(20) NOT NULL auto_increment,
  `vote_type` enum('links','comments') NOT NULL default 'links',
  `vote_date` timestamp NOT NULL,
  `vote_link_id` int(20) NOT NULL default '0',
  `vote_user_id` int(20) NOT NULL default '0',
  `vote_value` smallint(11) NOT NULL default '1',
  `vote_ip` varchar(64) default NULL,
  PRIMARY KEY  (`vote_id`),
  KEY `user_id` (`vote_user_id`),
  KEY `link_id` (`vote_link_id`),
  KEY `vote_type` (`vote_type`,`vote_link_id`,`vote_user_id`,`vote_ip`)
) TYPE = MyISAM;";
echo 'Creating table: \'votes\'...<br />';
mysql_query( $sql, $conn );

$sql = 'DROP TABLE IF EXISTS `' . table_pageviews . '`;';
mysql_query( $sql, $conn );

$sql = 'DROP TABLE IF EXISTS `' . table_config . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_config . "` (
  `var_id` int(11) NOT NULL auto_increment,
  `var_page` varchar(50) NOT NULL,
  `var_name` varchar(100) NOT NULL,
  `var_value` varchar(255) NOT NULL,
  `var_defaultvalue` varchar(50) NOT NULL,
  `var_optiontext` varchar(200) NOT NULL,
  `var_title` varchar(200) NOT NULL,
  `var_desc` text NOT NULL,
  `var_method` varchar(10) NOT NULL,
  `var_enclosein` varchar(5) default NULL,
  PRIMARY KEY  (`var_id`)
) TYPE = MyISAM;";
echo 'Creating table: \'config\'....<BR/>';
mysql_query( $sql, $conn );


$sql = 'DROP TABLE IF EXISTS `' . table_messages . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" .table_messages. "` (
  `idMsg` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `body` text NOT NULL,
  `sender` int(11) NOT NULL default '0',
  `receiver` int(11) NOT NULL default '0',
  `senderLevel` int(11) NOT NULL default '0',
  `readed` int(11) NOT NULL default '0',
  `date` timestamp NOT NULL,
  PRIMARY KEY  (`idMsg`)
) TYPE = MyISAM;";
echo 'Creating table: \'messages\'....<BR/>';
mysql_query( $sql, $conn );


$sql = 'DROP TABLE IF EXISTS `' . table_modules . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_modules . "` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `version` float NOT NULL,
  `latest_version` float NOT NULL,
  `folder` varchar(50) NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE = MyISAM;";
echo 'Creating table: \'modules\'....<BR/>';
mysql_query( $sql, $conn );


$sql = 'DROP TABLE IF EXISTS `' . table_formulas . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_formulas . "` (
  `id` int(11) NOT NULL auto_increment,
  `type` varchar(10) NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  `title` varchar(50) NOT NULL,
  `formula` text NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE = MyISAM;";
echo 'Creating table: \'formulas\'....<BR/>';
mysql_query( $sql, $conn );


$sql = 'DROP TABLE IF EXISTS `' . table_saved_links . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_saved_links ."` (
  `saved_id` int(11) NOT NULL auto_increment,
  `saved_user_id` int(11) NOT NULL,
  `saved_link_id` int(11) NOT NULL,
  `saved_privacy` ENUM( 'private', 'public' ) NOT NULL default 'public',
  PRIMARY KEY  (`saved_id`),
  KEY `saved_user_id` (  `saved_user_id` )
) TYPE = MyISAM;";
echo 'Creating table: \'Saved Links\'....<br />';
mysql_query( $sql, $conn );


$sql = 'DROP TABLE IF EXISTS `' . table_misc_data . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_misc_data . "` (
	`name` VARCHAR( 20 ) NOT NULL ,
	`data` TEXT NOT NULL ,
	PRIMARY KEY ( `name` )
	) TYPE = MyISAM;";
echo 'Creating table: \'Misc Data\'....<br />';
mysql_query( $sql, $conn );

////////////////////////////////////////////////////////////////////////////
//groups upgrade code inserting table
//group table
$sql = 'DROP TABLE IF EXISTS `' . table_groups . '`;';
mysql_query( $sql, $conn );
	$sql = "CREATE TABLE `".table_groups."` (
  `group_id` int(20) NOT NULL auto_increment,
  `group_creator` int(20) NOT NULL,
  `group_status` enum('Enable','disable') collate latin1_general_ci NOT NULL,
  `group_members` int(20) NOT NULL,
  `group_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `group_safename` text collate latin1_general_ci NOT NULL,
  `group_name` text collate latin1_general_ci NOT NULL,
  `group_description` text collate latin1_general_ci NOT NULL,
  `group_privacy` enum('private','public','restricted') collate latin1_general_ci NOT NULL,
  `group_avatar` varchar(255) collate latin1_general_ci NOT NULL,
  `group_vote_to_publish` int(20) NOT NULL,
  `group_field1` varchar(255) collate latin1_general_ci NOT NULL,
  `group_field2` varchar(255) collate latin1_general_ci NOT NULL,
  `group_field3` varchar(255) collate latin1_general_ci NOT NULL,
  `group_field4` varchar(255) collate latin1_general_ci NOT NULL,
  `group_field5` varchar(255) collate latin1_general_ci NOT NULL,
  `group_field6` varchar(255) collate latin1_general_ci NOT NULL,
	PRIMARY KEY  (`group_id`),
	KEY `group_name` (`group_name`(100)),
	KEY `group_creator` (`group_creator`, `group_status`)
	);";
echo 'Creating table: \'groups\'....<br />';
mysql_query( $sql, $conn );

//group member table
$sql = 'DROP TABLE IF EXISTS `' . table_group_member . '`;';
mysql_query( $sql, $conn );
$sql = "CREATE TABLE `".table_group_member."` (
	`member_id` INT( 20 ) NOT NULL auto_increment,
	`member_user_id` INT( 20 ) NOT NULL ,
	`member_group_id` INT( 20 ) NOT NULL ,
	`member_role` ENUM( 'admin', 'normal', 'moderator', 'flagged', 'banned' ) NOT NULL,
	`member_status` ENUM( 'active', 'inactive') NOT NULL,
	PRIMARY KEY  (`member_id`),
	KEY `user_group` (`member_group_id`, `member_user_id`)
	);";

mysql_query( $sql, $conn );
echo 'Creating table: \'group members\'....<br />';
//group shared table
$sql = 'DROP TABLE IF EXISTS `' . table_group_shared . '`;';
mysql_query( $sql, $conn );
$sql = "CREATE TABLE `".table_group_shared."` (
	`share_id` INT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	`share_link_id` INT( 20 ) NOT NULL ,
	`share_group_id` INT( 20 ) NOT NULL ,
	`share_user_id` INT( 20 ) NOT NULL,
	KEY `share_group_id` (`share_group_id`));";

echo 'Creating table: \'group shared\'....<br />';
mysql_query( $sql, $conn );
///////////////////////////////////////////////////////////////////////////

$sql = "INSERT INTO `" . table_misc_data . "` ( `name` , `data` ) VALUES ('pligg_version', '1.0.0');";
mysql_query( $sql, $conn );
//Captcha Upgrade:
$sql = "INSERT INTO `" . table_misc_data . "` ( `name` , `data` ) VALUES ('captcha_method', 'reCaptcha');";
mysql_query($sql,$conn);
$sql = "INSERT INTO `" . table_misc_data . "` ( `name` , `data` ) VALUES ('reCaptcha_pubkey', '6LfwKQQAAAAAAPFCNozXDIaf8GobTb7LCKQw54EA');";
mysql_query($sql,$conn);
$sql = "INSERT INTO `" . table_misc_data . "` ( `name` , `data` ) VALUES ('reCaptcha_prikey', '6LfwKQQAAAAAALQosKUrE4MepD0_kW7dgDZLR5P1');";
mysql_query($sql,$conn);
//
//register validation//
$randkey = rand(1000000,100000000);

$sql = "INSERT INTO `" . table_misc_data . "` ( `name` , `data` ) VALUES ('hash', $randkey);";
mysql_query( $sql, $conn );

$sql = "INSERT INTO `" . table_misc_data . "` ( `name` , `data` ) VALUES ('validate', 0);";
mysql_query( $sql, $conn );
//
$sql = 'DROP TABLE IF EXISTS `' . table_totals . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_totals . "` (
	`name` varchar(10) NOT NULL,
	`total` int(11) NOT NULL,
	PRIMARY KEY  (`name`)
	) TYPE = MyISAM;";

echo 'Creating table: \'Totals\'....<br />';
mysql_query( $sql, $conn );

$sql = 'DROP TABLE IF EXISTS `' . table_tag_cache . '`;';
mysql_query( $sql, $conn );

$sql = "CREATE TABLE `" . table_tag_cache . "` (
	  `tag_words` varchar(64) NOT NULL,
	  `count` int(11) NOT NULL
	) TYPE=MyISAM";

echo 'Creating table: \'Tag cache\'....<br />';
mysql_query( $sql, $conn );


$sql = "CREATE TABLE `" . table_redirects . "` (
  `redirect_id` int(11) NOT NULL auto_increment,
  `redirect_old` varchar(255) NOT NULL,
  `redirect_new` varchar(255) NOT NULL,
  PRIMARY KEY  (`redirect_id`),
  KEY `redirect_old` (`redirect_old`)
	) TYPE = MyISAM;";
mysql_query( $sql, $conn );


echo 'Inserting default category...<br />';
$sql = "INSERT INTO `" . table_categories . "` VALUES (0, '" . $dblang . "', 0, 0, 'all', 'all', 0, 0, 2, 0, '', '', 'normal', '');";
mysql_query( $sql, $conn );

$sql = "UPDATE `" . table_categories . "` SET `category__auto_id` = '0' WHERE `category_name` = 'all' LIMIT 1;";
mysql_query( $sql, $conn );

$sql = "INSERT INTO `" . table_categories . "` VALUES (1, '" . $dblang . "', 1, 0, 'News', 'News', 0, 0, 1, 0, '', '', 'normal', '');";
mysql_query( $sql, $conn );

echo 'Inserting default modules...<br />';
$sql = "INSERT INTO `" . table_modules . "` VALUES (2, 'Admin Modify Language', 0.1, '', 'admin_language', 1);";
mysql_query( $sql, $conn );
$sql = "INSERT INTO `" . table_modules . "` VALUES (3, 'Captcha', 0.1, '', 'captcha', 1);";
mysql_query( $sql, $conn );
$sql = "INSERT INTO `" . table_modules . "` VALUES (4, 'Multibox Admin', 0.1, '', 'multibox_admin', 1);";
mysql_query( $sql, $conn );
$sql = "INSERT INTO `" . table_modules . "` VALUES (5, 'Admin Help English', 0.2, '', 'admin_help_english', 1);";
mysql_query( $sql, $conn );
$sql = "INSERT INTO `" . table_modules . "` VALUES (6, 'Hello World', 0.2, '', 'hello_world', 1);";
mysql_query( $sql, $conn );
$sql = "INSERT INTO `" . table_modules . "` VALUES (7, 'Simple Private Messaging', 0.2, '', 'simple_messaging', 1);";
mysql_query( $sql, $conn );

echo 'Inserting default formulas...<br />';
$sql = 'INSERT INTO `' . table_formulas . '` VALUES (1, \'report\', 1, \'Simple Story Reporting\', \'$reports > $votes * 3\');';
mysql_query( $sql, $conn );

echo "Inserting default 'totals' data...<br />";
	$sql = "insert into `" . table_totals . "` (`name`, `total`) values ('published', 0);";
	mysql_query( $sql, $conn );
	
	$sql = "insert into `" . table_totals . "` (`name`, `total`) values ('queued', 0);";	
	mysql_query( $sql, $conn );

	$sql = "insert into `" . table_totals . "` (`name`, `total`) values ('discard', 0);";	
	mysql_query( $sql, $conn );

echo "Inserting default 'config' data...<br />";

$stmts = explode(';', file_get_contents(dirname(__FILE__) . '/upgrade_config_table.sql'));
foreach($stmts as $stmt) {
  $stmt = str_replace("INSERT INTO `config`", "INSERT INTO `".table_config."`", $stmt);
  $stmt = str_replace("'table_prefix', 'pligg_'", "'table_prefix', '" . table_prefix . "'", $stmt);
  mysql_query($stmt);
}

return 1;
}

?>
